
const xlsx = require('node-xlsx');
const db = require('db');
const log = require('tools/log');

const options = {
    '!cols': [
        { wch: 7 },
        { wch: 7 },
        { wch: 10 },
        { wch: 10 },
        { wch: 15 },
        { wch: 10 },
        { wch: 30 },
        { wch: 10 },
        { wch: 10 },
        { wch: 10 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
    ]
};

module.exports = async (ctx, next) => {

    const { connection, run } = db.init();
    try {
        const list = await orderlist(run);
        ctx.status = 200;
        ctx.body = xlsx.build([{ name: "结算单", data: format(list), options }]);
    } catch (error) {
        ctx.status = error.status;
        ctx.body = error.err;
        log('---------------------------------------------------')
        console.log(error)
    }
}


/** 获取列表 */
async function orderlist(run) {
    const sqlStr = `
        SELECT 
            o.company,
            o.commercialSpecialistName,
            o.pmName,
            o.orderStatus,
            o.orderTime,
            o.orderNo,
            o.deptName,
            c.clientName,
            o.project,
            o.cooperationMode,
            o.undertakingType,
            o.planStartTime,
            o.startTime,
            o.scheduledCompletionTime,
            o.completionTime,
            o.price,
            o.personDays,
            o.businessDay,
            o.paymentCycle,
            o.details,
            o.settlementAmount,
            s.settlementNo, 
            s.settlementState,
            s.invoiceTime,
            s.repaymentTime
        FROM orders AS o, settlement AS s, settlementEntry AS e, client AS c
        WHERE o.id = e.ordersId AND e.settlementId = s.id AND o.clientId = c.id
    `
    const result = await run(sqlStr);
    if (result.err) {
        throw {
            status: 500,
            err: result.err,
        }
    }

    return result.rows;
}

/** 数据格式 */
function format(list) {
    const obj = list.map(e => ({
        '公司': companyStringMap[e.company] || e.company,
        '商务': e.commercialSpecialistName,
        'PM': e.pmName,
        '订单状态': e.orderStatus,
        '结算状态': e.settlementState,
        '接单时间': e.orderTime,
        '订单编号': e.orderNo,
        '结算单编号': e.settlementNo,
        '归属部门': e.deptName,
        '客户全称': e.clientName,
        '项目名称': e.project,
        '合作模式': e.cooperationMode,
        '承接类型': e.undertakingType,
        '计划开始时间': e.planStartTime,
        '实际开始时间': e.startTime,
        '计划完成时间': e.scheduledCompletionTime,
        '实际完成时间': e.completionTime,
        '开票时间': e.invoiceTime,
        '回款时间': e.repaymentTime,
        '单价（元）': e.price,
        '制作人天': e.personDays,
        '商务人天': e.businessDay,
        '总金额（元）': { f: '=P{i}*R{i}' },
        '结算金额': e.settlementAmount,
        '付款周期（天）': e.paymentCycle,
        '订单明细': e.details,
    }))

    const keys = Object.keys(obj[0]);

    const values = obj
        .map(e => Object.values(e))
        .map((a, i) => {
            return a.map(e => {
                return replace(e, /\{i\}/g, i + 2);
            });
        })

    return [
        keys,
        ...values
    ]
}

const replace = (e, regExp, replaceValue) => {
    if (typeof e === 'string') {
        return e.replace(regExp, replaceValue);
    } else if (e && typeof e.f === 'string') {
        e.f = e.f.replace(regExp, replaceValue);
        return e;
    }
    return e;
}

const companyStringMap = {
    'hh': '火猴',
    'ydw': '悦逗蛙',
}